package com.pointcircle.estate.server.framework.repository;

import java.util.List;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.pointcircle.core.IRepository;
import com.pointcircle.core.entity.User;
import com.pointcircle.estate.server.framework.entity.Building;
import com.pointcircle.estate.server.framework.entity.House;
import com.pointcircle.estate.server.framework.entity.ProblemClass;
import com.pointcircle.estate.server.framework.entity.QualityMeasureCheckQualityProblem;

@Repository
public interface QualityMeasureCheckQualityProblemRepository extends IRepository<QualityMeasureCheckQualityProblem, String> {

	List<QualityMeasureCheckQualityProblem> findByHouseAndProblemClass(House house, ProblemClass problemClass);
	
	//List<QualityMeasureCheckQualityProblem> findByRegistBy(User user);
	
	List<QualityMeasureCheckQualityProblem> findByRegistBy(User registBy);
	
	List<QualityMeasureCheckQualityProblem> findByRepairBy(User repairBy);	
	
	List<QualityMeasureCheckQualityProblem> findByBuilding(Building building);	
	
	List<QualityMeasureCheckQualityProblem> findByBuildingAndProblemClass(
		Building building, ProblemClass problemClass);
	
	@Query("select o from QualityMeasureCheckQualityProblem o where o.id in (:problemIds)")
	List<QualityMeasureCheckQualityProblem> findProblemsByProblemIds(@Param("problemIds") List<String> problemIds);
	
	@Query(value = "select * from tb_quality_measure_checkquality_problem t " + 
			"where t.regist_date > str_to_date(?2, '%Y%m%d') and quality_measure_checkquality_problem_id in " + 
			"( SELECT quality_measure_checkquality_problem_id FROM tb_quality_measure_checkquality_cc q "
			+ "WHERE q.cc = ?1 );", nativeQuery = true)
	List<QualityMeasureCheckQualityProblem> findByCcAndDate(String ccId, String date);
	
	@Query(value = "select * from tb_quality_measure_checkquality_problem t " + 
			"where t.regist_date >= str_to_date(?2, '%Y%m%d') "
			+ "and t.regist_date < str_to_date(?3, '%Y%m%d')"
			+ "and quality_measure_checkquality_problem_id in " + 
			"( SELECT quality_measure_checkquality_problem_id FROM tb_quality_measure_checkquality_cc q "
			+ "WHERE q.cc = ?1 );", nativeQuery = true)
	List<QualityMeasureCheckQualityProblem> findByCcAndTwoDate(String ccId, String startDate, String endDate);
	
	
	@Query(value = "SELECT o FROM QualityMeasureCheckQualityProblem o where o.floorId=:floorId and o.itemId=:itemId")
	List<QualityMeasureCheckQualityProblem> findProblemsByFloorIdAndItemId(
		@Param("floorId") String floorId, @Param("itemId") String itemId);

	@Query(value="select item_id, unit_id, round((sum(measure_points)-sum(problem_points))/sum(measure_points)*100,0) sate "
			+ "from tb_quality_measure_checkquality_problem GROUP BY item_id,unit_id ", nativeQuery = true)
	List<Object[]> findQualityMeasureRates();
	
	
	@Query(value = "select * from tb_quality_measure_checkquality_problem t "
			+ "WHERE t.regist_by = ?1 "
			+ "OR quality_measure_checkquality_problem_id IN ("
			+ " SELECT quality_measure_checkquality_problem_id "
			+ " FROM tb_quality_measure_checkquality_repair"
			+ " WHERE REPAIR = ?1)", nativeQuery = true)
	List<QualityMeasureCheckQualityProblem> findByRegistBy(String userId);
}
